10장. 실행 계획

통계 정보

테이블 및 인덱스에 대한 통계 정보를 뜻한다.

MySQL 서버의 통계 정보

MySQL 5.6 버전부터 InnoDB 스토리지 엔진을 사용하는 테이블에 대한 통계 정보를 영구적으로 관리할 수 있도록 개선되었다. 즉, MySQL 서버가 재시작돼도 기존의 통계 정보를 유지할 수 있게 되었다.

innodb_stats_auto_recalc 시스템 설정 변수의 값을 OFF 로 설정해서 통계 정보가 자동으로 갱신되는 것을 막을 수 있다. innodb_stats_auto_recalc 시스템 설정 변수의 기본값은 ON 이므로 영구적인 통계 정보를 이용하고자 한다면 해당 설정을 OFF 로 변경할 수 있다.

히스토그램

5.7 버전까지 통계 정보는 단순히 인덱스된 칼럼의 유니크한 값의 개수 정도만 가지고 있었는데, 이는 옵티마이저가 최적의 실행 계획을 수립하기에는 많이 부족했다. 이를 메우기 위해 실행 게획을 수립할 때 실제 인덱스의 일부 페이지를 랜덤으로 가져와 참조하는 방식을 사용했다.

8.0 버전부터 칼럼의 데이터 분포도를 참조할 수 있는 히스토그램 정보를 활용할 수 있게 되었다.

히스토그램 정보 수집 및 삭제

히스토그램 정보는 칼럼 단위로 관리되는데, 이는 자동으로 수집되지 않고 ANALYZE TABLE ... UPDATE HISTOGRAM 명령을 수행해 수동으로 수집 및 관리된다. 수집된 히스토그램 정보는 시스템 딕셔너리에 함께 저장되고, MySQL 서버가 시작될 때 딕셔너리의 히스토그램 정보를 information_schema 데이터베이스의 column_statistics 테이블로 로드한다.

다음과 같은 두 종류의 히스토그램 타입이 지원된다.

• singleton: 컬럼값 개별로 레코드 건수를 관리하는 히스토그램. Value-based 히스토그램이나 도수 분포라고도 불린다.
• Equi-Height: 컬럼값의 범위를 균등한 개수로 구분해서 관리하는 히스토그램으로 Height-Balanced 히스토그램이라고도 불린다.

히스토그램은 버킷 단위로 구분되어 레코드 건수나 컬럼값의 범위가 관리된다. 싱글톤 히스토그램은 컬럼이 가지는 값별로 버킷이 할당되며, 높이 균형 히스토그램에서는 개수가 균등한 컬럼값의 범위별로 하나의 버킷이 할당된다.

싱글톤 히스토그램은 각 버킷이 컬럼의 값과 발생 빈도 비율, 2개 값을 갖는다.
반면 높이 균형 히스토그램은 각 버킷이 범위 시작 값과 마지막 값, 그리고 발생 빈도율과 각 버킷에 포함된 유니크한 값의 개수 등 4개의 값을 갖는다.

싱글톤 히스토그램은 주로 코드 값과 같이 유니크한 값의 개수가 상대적으로 적은 (히스토그램의 버킷 수보다 적은) 경우 사용된다.

• [!] 히스토그램의 모든 레코드 건수 비율은 누적으로 표시된다.

높이 균형 히스토그램은 컬럼값의 각 범위에 대해 레코드 건수 비율이 누적으로 표시된다. 히스토그램의 버킷 범위가 뒤로 갈수록 비율이 높아지는 것으로 보이지만, 사실은 범위별로 비율이 같은 수준에서 hire_date 컬럼의 범위가 선택된 것이다. (즉, 그래프의 기울기가 일정하면 각 범위가 비슷한 값을 갖는다는 것을 알 수 있다.)

생성된 히스토그램은 DROP HISTOGRAM ON 컬럼 이름 으로 삭제할 수 있다. 히스토그램의 삭제 작업은 테이블의 데이터를 참조하는 것이 아닌, 딕셔너리의 내용만 삭제하기 때문에 다른 쿼리 처리의 성능에 영향을 주지 않고 즉시 완료된다. 그러나 히스토그램이 사라지면 쿼리의 실행 계획이 달라질 수 있으므로 주의해야 한다.

히스토그램을 삭제하지 않고 MySQL 옵티마이저가 히스토그램을 사용하지 않게 하려면 optimizer_switch 시스템 변수의 값을 변경하면 된다.

SET GLOBAL optimizer_switch='condition_fanout_filter=off';

optimizer_switch 시스템 변수의 값을 글로벌로 변경하면 MySQL 서버의 모든 쿼리가 히스토그램을 사용하지 않는다. 또한 condition_fanout_filter 옵션에 의해 영향받는 다른 최적화 기능들이 사용되지 않을 수도 있으니 주의하자.

특정 커넥션 또는 특정 쿼리에서만 히스토그램을 사용하지 않고자 한다면 다음과 같은 방법을 사용하면 된다.

-- // 현재 커넥션에서 실행되는 쿼리만 히스토그램을 사용하지 않도록 설정

SET SESSION optimizer_switch='condition_fanout_filter=off';

-- // 현재 쿼리만 히스토그램을 사용하지 않도록 설정

SELECT /*+ SET_VAR(optimizer_switch='condition_fanout_filter=off') */ *
FROM ...

히스토그램의 용도

기존 MySQL 서버가 가지고 있던 통계 정보는 테이블의 전체 레코드 건수와 인덱스된 컬럼이 갖는 유니크한 값의 개수 정도였다. 예를 들면 테이블의 레코드가 1000건이고 어떤 컬럼의 유니크한 값 개수가 100개였다면 MySQL 서버는 해당 컬럼에 대해 다음과 같은 동등 비교 검색을 하면 대략 10개의 레코드가 일치할 것이라고 예측한다.

그러나 실제 응용 프로그램의 데이터는 항상 균등한 분포도를 가지지 않는데, 이전 MySQL 서버의 기존 통게 정보는 이런 정보를 고려하지 못했다. 이를 보완하기 위해 히스토그램이 도입되었다. 특정 컬럼이 가지는 모든 값에 대한 분포도 정보를 가지지는 않지만 각 버킷 별로 레코드의 건수와 유니크한 값의 개수 정보를 갖기 때문에 훨씬 정확한 예측을 할 수 있다.

히스토그램 정보가 없으면 옵티마이저는 데이터가 균등하게 분포돼 있을 것이라고 예측한다. 하지만 히스토그램 정보가 있으면 특정 범위의 데이터가 많고 적음을 식별할 수 있다.

인덱싱되지 않은 컬럼들에 히스토그램이 없다면 옵티마이저는 컬럼들의 데이터 분포를 전혀 알지 못하고 실행 계획을 수립하게 된다. 상황에 따라 어떤 테이블이라도 조인의 드라이빙 테이블이 될 수 있는 것이다.

이런 차이로 인해 쿼리의 성능은 10배 정도 차이를 보일 수 있으며, InnoDB 버퍼 풀에 데이터가 존재하지 않아서 디스크에서 데이터를 읽어야 하는 경우라면 몇 배의 차이가 발생할 수도 있다. 각 컬럼에 대해 히스토그램 정보가 있다면 어느 테이블을 먼저 읽어야 조인의 횟수를 줄일 수 있을지 옵티마이저가 더 정확히 판단할 수 있다.

히스토그램과 인덱스

히스토그램과 인덱스는 완전히 다른 객체이므로 서로 비교할 대상은 아니지만, MySQL 서버에서 인덱스는 부족한 통계 정보를 수집하기 위해 사용된다는 점에서 어느 정도 공통점을 가진다고 볼 수 있다.

MySQL 서버에서는 쿼리의 실행 계획을 수립할 때 사용 가능한 인덱스들로부터 조건절에 일치하는 레코드 건수를 대략 파악하고 최종적으로 가장 나은 실행 계획을 선택한다. 이때 조건절에 일치하는 레코드 건수를 예측하기 위해 옵티마이저는 실제 인덱스의 B-Tree 를 샘플링해서 살펴본다. 이 작업을 인덱스 다이브 (Index Dive) 라고 표현한다.

쿼리의 검색 조건으로 많이 사용되는 컬럼에 대해서는 일반적으로 인덱스를 생성한다. 그런데 이렇게 인덱스된 컬럼에 대해 히스토그램 정보를 수집해두는 것이 좋은지 고민스러울 수 있다.

MySQL 8.0 서버에서는 인덱스된 컬럼을 검색 조건으로 사용하는 경우, 그 컬럼의 히스토그램은 사용하지 않고 실제 인덱스 다이브를 통해 직접 수집한 정보를 활용한다. 이는 실제 검색 조건의 대상 값에 대한 샘플링을 실행하는 것이므로 항상 히스토그램보다 정확한 결과를 기대할 수 있기 때문이다. 즉, MySQL 8.0 버전에서 히스토그램은 주로 인덱스되지 않은 컬럼에 대한 데이터 분포도를 참조하는 용도로 사용된다.

그러나 인덱스 다이브 작업은 어느 정도의 비용이 필요하며, 때로는 (IN 절에 값이 많이 명시된 경우) 실행 계획 수립만으로도 상당한 인덱스 다이브를 실행하고 비용도 그만큼 커진다.

코스트 모델

MySQL 서버가 쿼리를 처리하려면 다양한 작업이 필요하다.

• 디스크로부터 데이터 페이지 읽기
• 메모리 (InnoDB 버퍼 풀)로부터 데이터 페이지 읽기
• 인덱스 키 비료
• 레코드 평가
• 메모리 임시 테이블 작업
• 디스크 임시 테이블 작업

MySQL 서버는 사용자의 쿼리에 대해 이러한 다양한 작업이 얼마나 필요한지 예측하고 전체 작업 비용을 계산한 결과를 바탕으로 최적의 실행 계획을 찾는다. 이렇게 전체 쿼리의 비용을 계산하는 데 필요한 단위 작업들의 비용을 코스트 모델이라고 한다.

MySQL 5.7 버전부터 MySQL 서버의 소스 코드에 상수화되어 있던 각 단위 작업의 비용을 DBMS 관리자가 조정할 수 있도록 개선되었다. 그러나 인덱스되지 않은 컬럼의 데이터 분포나 메모리에 상주 중인 페이지의 비율 등 비용 계산과 연관된 부분의 정보가 부족한 상태였다. MySQL 8.0 버전으로 업그레이드 되면서 비로소 컬럼의 데이터 분포를 위한 히스토그램과 각 인덱스별 메모리에 적재된 페이지의 비율이 관리되고 옵티마이저의 실행 계획 수립에 사용되기 시작했다.

MySQL 8.0 서버의 코스트 모델은 다음 2개 테이블에 저장돼 있는 설정 값을 사용한다. 두 테이블 모두 mysql DB 에 존재한다.

• server_cost: 인덱스를 찾고 레코드를 비교하고 임시 테이블 처리에 대한 비용 관리
• engine_cost: 레코드를 가진 데이터 페이지를 가져오는 데 필요한 비용 관리

두 테이블은 공통적으로 다음의 5개 컬럼을 가지고 있다. last_updated 와 comment 컬럼의 값은 MySQL 서버 옵티마이저에 영향을 미치는 정보는 아니고, 단순 정보성으로 관리되는 컬럼이다.

• cost_name: 코스트 모델의 각 단위 작업
• default_value: 각 단위 작업의 비용(기본값이며, 이 값은 MySQL 서버 소스 코드에 설정된 값)
• cost_value: DBMS 관리자가 설정한 값 (이 값이 NULL 이면 MySQL 서버는 default_value 컬럼의 비용을 사용한다.)
• last_updated: 단위 작업의 비용이 변경된 시점
• comment: 비용에 대한 추가 설명

engine_cost 테이블은 위의 5개 컬럼에 추가로 다음 컬럼들을 더 가지고 있다.

• engine_name: 비용이 적용된 스토리지 엔진
• device_type: 디스크 타입

engine_name 컬럼은 스토리지 엔진별로 각 단위 작업의 비용을 설정할 수 있는데, 기본값은 default 이다. MEMORY 스토리지 엔진, 그리고 MyISAM 과 InnoDB 스토리지 엔진에 대해 단위 작업의 비용을 달리 설정하고 싶다면 engine_name 컬럼을 이용하면 된다. device_type 은 디스크의 타입을 설정할 수 있는데, MySQL 8.0 에서는 아직 이 컬럼의 값을 활용하지 않아 "0" 만 설정할 수 있다.

MySQL 8.0 버전의 코스트 모델에서 지원하는 단위 작업은 다음과 같다.

• io_block_read_cost: 디스크 데이터 페이지 읽기
• memory_block_read_cost: 메모리 데이터 페이지 읽기
• disk_temptable_create_cost: 디스크 임시 테이블 생성
• disk_temptable_row_cost: 디스크 임시 테이블의 레코드 읽기
• key_compare_cost: 인덱스 키 비교
• memory_temptable_create_cost: 메모리 임시 테이블 생성
• memory_temptable_row_cost: 메모리 임시 테이블의 레코드 읽기
• row_evaluate_cost: 레코드 비교

row_evaluate_cost 는 스토리지 엔진이 반환한 레코드가 쿼리의 조건에 일치하는지를 평가하는 단위 작업을 의미한다. row_evaluate_cost 값이 증가할수록 풀 테이블 스캔과 같이 많은 레코드를 처리하는 쿼리의 비용이 높아지고 반대로 레인지 스캔과 같이 상대적으로 적은 수의 레코드를 처리하는 쿼리의 비용이 낮아진다.

코스트 모델에서 중요한 것은 각 단위 작업에 설정되는 비용 값이 커지면 어떤 실행 계획들이 고비용으로 바뀌고, 어떤 실행 계획들이 저비용으로 바뀌는지를 파악하는 것이다.

• key_compare_cost 비용을 높이면 MySQL 서버 옵티마이저가 가능하면 정렬을 수행하지 않는 방향의 실행 계획을 선택할 가능성이 높아진다.
• row_evaluate_cost 비용을 높이면 풀 스캔을 실행하는 쿼리들의 비용이 높아지고, MySQL 서버 옵티마이저는 가능하면 인덱스 레인지 스캔을 사용하는 실행 계획을 선택할 가능성이 높아진다.
• disk_temptable_create_cost 와 disk_temptable_row_cost 비용을 높이면 MySQL 옵티마이저는 디스크에 임시 테이블을 만들지 않는 방향의 실행 계획을 선택할 가능성이 높아진다.
• memory_temptable_create_cost 와 memory_temptable_row_cost 비용을 높이면 MySQL 서버 옵티마이저는 메모리 임시 테이블을 만들지 않는 방향의 실행 계획을 선택할 가능성이 높아진다.
• io_block_read_cost 비용이 높아지면 MySQL 서버 옵티마이저는 가능하면 InnoDB 버퍼 풀에 데이터 페이지가 많이 적재돼 있는 인덱스를 사용하는 실행 계획을 선택할 가능성이 높아진다.
• memory_block_read_cost 비용이 높아지면 MySQL 서버는 InnoDB 버퍼 풀에 적재된 데이터 페이지가 상대적으로 적다고 하더라도 그 인덱스를 사용할 가능성이 높아진다.

실행 계획 확인

DESC 또는 EXPLAIN 명령으로 확인할 수 있다.

FORMAT 옵션을 사용해서 실행 계획의 표시 방법을 JSON 이나 TREE, 단순 테이블 형태로 선택할 수 있다.

EXPLAIN FORMAT=TREE -- 혹은 JSON
...

쿼리의 실행 시간 확인

쿼리의 실행 계획과 단계별 소요된 시간 정보를 확인할 수 있는 EXPLAIN ANALYZE 기능이 추가됐다. 결과는 항상 TREE 포맷으로 보여준다.

명령의 결과에는 실제 소요된 시간, 처리한 레코드 건수, 반복 횟수가 표시된다.

• actual time: 첫 번째 숫자 값은 첫 번째 레코드를 가져오는 데 걸린 평균 시간 (밀리초), 두 번째 숫자 값은 마지막 레코드를 가져오는 데 걸린 평균 시간 (밀리초)
• rows: 테이블에서 읽은 조건에 일치하는 테이블의 평균 레코드 건수
• loops: 테이블에서 읽은 컬럼을 이용해서 테이블의 레코드를 찾는 작업이 반복된 횟수

EXPLAIN ANALYZE 는 실행 계획만 추출하는 것이 아닌 실제 쿼리를 실행하고 사용된 실행 계획과 소요된 시간을 보여준다.

실행 계획 분석

표의 각 라인은 쿼리 문장에서 사용된 테이블의 개수만큼 출력된다. 실행 순서는 위에서 아래로 순서대로 표시된다. 출력된 실행 계획에서 위쪽에 출력된 결과일수록 쿼리의 바깥부분이거나 먼저 접근한 테이블이고, 아래쪽에 출력된 결과일수록 쿼리의 안쪽 부분 또는 나중에 접근한 테이블에 해당한다.

id 컬럼

단위 SELECT 쿼리별로 부여되는 식별자 값이다. 하나의 SELECT 문장 안에서 여러 개의 테이블을 조인하면 조인되는 테이블의 개수만큼 실행 계획 레코드가 출력되지만 같은 id 값이 부여된다.

실행 계획의 id 컬럼은 테이블의 접근 순서를 의미하지는 않는다.

select_type 컬럼

각 단위 SELECT 쿼리가 어떤 타입의 쿼리인지 표시되는 컬럼이다.

• SIMPLE
  • UNION 이나 서브 쿼리를 사용하지 않는 단순한 SELECT 쿼리인 경우 해당 쿼리 문장의 select_type 은 SIMPLE 로 표시된다. (쿼리에 조인이 포함된 경우도 마찬가지) 쿼리 문장이 아무리 복잡하더라도 실행 계획에서 select_type 이 SIMPLE인 단위 쿼리는 하나만 존재한다.일반적으로 제일 바깥 SELECT 쿼리의 select_type 이 SIMPLE 로 표시된다.
• PRIMARY
  • UNION 이나 서브 쿼리를 가지는 SELEcCT 쿼리의 실행 계획에서 가장 바깥쪽에 있는 단위 쿼리는 select_type 이 PRIMARY 로 표시된다. SIMPLE 과 마찬가지로 select_type 이 PRIMARY 인 단위 쿼리는 하나만 존재하며, 쿼리의 제일 바깥쪽에 있는 SELECT 단위 쿼리가 PRIMARY 로 표시된다.
• UNION
  • UNION 으로 결합하는 단위 SELECT 쿼리 가운데 첫 번째를 제외한 두 번째 이후 단위 SELECT 쿼리의 select_type 은 UNION 으로 표시된다.
  • UNION의 첫 번째 단위 SELECT 는 select_type 이 UNION 이 아니라 UNION 되는 쿼리 결과들을 모아서 저장하는 임시 테이블 (DERIVED) 이 select_type 으로 표시된다.
• DEPENDENT UNION
  • UNION 과 같이 UNION 이나 UNION ALL 로 집합을 결합하는 쿼리에서 표시된다.
  • DEPENDENT 는 UNION 이나 UNION ALL 로 결합된 단위 쿼리가 외부 쿼리에 의해 영향을 받는 것을 의미한다. 즉, 내부 쿼리가 외부의 값을 참조해서 처리 될 때 DEPENDENT 키워드가 표시된다.
• UNION RESULT
  • UNION RESULT 는 UNION 결과를 담아두는 테이블을 의미한다. 실제 쿼리에서 단위 쿼리가 아니기 때문에 별도의 id 값은 부여되지 않는다. (id 가 null 임)
  • UNION RESULT 의 table 컬럼의 <union 숫자1, 숫자2> 는 id 가 각각 숫자1, 숫자2인 단위 쿼리의 조회 결과를 UNION 했음을 의미한다.
  • UNION ALL 을 사용하면 MySQL 은 임시 테이블에 버퍼링하지 않기 때문에 UNION RESULT 라인이 필요치 않게 된다.
• SUBQUERY
  • FROM 절 이외에서 사용하는 서브 쿼리만을 의미한다.
  • FROM 절에 사용된 서브쿼리는 select_type 이 DERIVED 로 표시되고, 그 밖의 위치에서 사용된 서브쿼리는 모두 SUBQUERY 라고 표시된다.

• [i] 서브 쿼리는 사용하는 위치에 따라 각각 다른 이름을 지니고 있다.
• 중첩된 쿼리: SELECT 되는 컬럼에 사용된 서브 쿼리를 Nested Query 라고 한다.
• 서브 쿼리: WHERE 절에 사용된 경우에는 일반적으로 그냥 SubQuery 라고 한다.
• 파생 테이블: FROM 절에 사용된 서브 쿼리를 MySQL 에서는 파생 테이블이라고 하고, 일반적으로 RDBMS 에서는 인라인 뷰 또는 서브 셀렉트 (Sub Select) 라고 한다.

서브쿼리가 반환하는 값의 특성에 따라 다음과 같이 구분하기도 한다.

• 스칼라 서브쿼리: 하나의 값만 반환하는 쿼리
• 로우 서브쿼리: 컬럼의 개수와 관계없이 하나의 레코드만 반환하는 쿼리

• DEPENDENT SUBQUERY
  • 서브 쿼리가 outer SELECT 쿼리에서 정의된 컬럼을 사용하는 경우, DEPENDENT SUBQUERY 가 표시된다.
  • DEPENDENT UNION 과 같이 DEPENDENT SUBQUERY 또한 외부 쿼리가 먼저 수행된 후 내부 쿼리가 실행되어야 하므로 DEPENDENT 키워드가 없는 일반 서브쿼리보다는 처리 속도가 느릴 때가 많다.
• DERIVED
  • 단위 SELECT 쿼리의 실행 결과로 메모리나 디스크에 임시 테이블을 생성하는 것을 의미한다. select_type 이 DERIVED 인 경우에 생성되는 임시 테이블을 파생 테이블이라고도 한다. MySQL 5.6 버전부터는 옵티마이저 옵션에 따라 쿼리의 특성에 맞게 임시 테이블에도 인덱스를 추가해서 만들 수 있게 최적화됐다.

• [p] 쿼리를 튜닝하기 위해 실행 계획을 확인할 때 가장 먼저 DERIVED 가 존재하는지 확인해야 한다. 서브쿼리를 조인으로 해결할 수 있는 경우라면 서브쿼리보다는 조인을 사용하는 것을 강력히 권장한다.

• DEPENDENT DERIVED
  • LATERAL JOIN 기능이 추가되면서 FROM 절의 서브쿼리에서도 외부 컬럼을 참조할 수 있게 됐다. DEPENDENT DERIVED 키워드는 해당 테이블이 LATERAL JOIN 으로 사용된 것을 의미한다.
• UNCACHEABLE SUBQUERY
  • 조건이 똑같은 서브쿼리가 실행될 때는 다시 실행하지 않고 이전의 실행 결과를 그대로 사용할 수 있께 서브쿼리의 결과를 내부적인 캐시 공간에 담아둔다. (서브쿼리 캐시는 쿼리 캐시나 파생 테이블과는 전혀 무관한 기능이다.)
  • SUBQUERY 는 outer 의 영향을 받지 않으므로 처음 한 번만 실행해서 그 결과를 캐시하고 필요할 때 캐시된 결과를 이용한다.
  • DEPENDENT SUBQUERY 는 의존하는 Outer 쿼리의 컬럼의 값 단위로 캐시해두고 사용한다.
  • 서브쿼리에 포함된 요소에 의해 캐시 자체가 불가능한 경우에 사용된다.
    • 사용자 변수가 서브쿼리에 사용된 경우
    • NOT-DETERMINISTIC 속성의 스토어드 루틴이 서브쿼리 내에 사용된 경우
    • UUID() 나 RAND() 같이 결괏값이 호출할 때마다 달라지는 함수가 서브쿼리에 사용된 경우
• UNCACHEABLE UNION
  • UNION, UNCACHEABLE 의 속성이 혼합된 select_type 을 의미한다.
• MATERIALIZED
  • MySQL 5.6 버전부터 도입된 select_type 으로, FROM 절이나 IN(subquery) 형태의 쿼리에 사용된 서브쿼리의 최적화를 위해 사용된다.
  • MySQL 5.7 버전부터는 서브쿼리의 내용을 임시 테이블로 구체화한 후, 임시 테이블과 다른 테이블을 조인하는 형태로 최적화되어 처리된다.

table 컬럼

MySQL 서버의 실행 계획은 단위 SELECT 쿼리 기준이 아닌 테이블 기준으로 표시된다. 테이블 이름에 별칭이 부여된 경우에는 별칭이 표시된다.

'<>' 로 둘러싸인 이름이 명시되는 경우는 임시 테이블을 의미한다.

partitions 컬럼

파티션이 여러 개인 테이블에서 불필요한 파티션을 빼고 쿼리를 수행하기 위해 접근해야 할 것으로 판단되는 테이블만 골라내는 과정을 파티션 프루닝이라고 한다.

파티션을 참조하는 쿼리의 경우 옵티마이저가 쿼리 처리를 위해 필요한 파티션들의 목록만 모아서 실행 계획의 partitions 컬럼에 표시해준다.

type 컬럼

일반적으로 쿼리를 튜닝할 때 인덱스를 효율적으로 사용하는지 확인하는 것이 중요하므로 실행 계획에서 type 컬럼은 반드시 체크해야 하는 중요한 정보다.

type 컬럼의 값은 각 테이블의 접근 방법으로 해석하면 된다.

ALL 은 인덱스를 사용하지 않고 테이블을 처음부터 끝까지 읽어서 레코드를 읽는 풀 테이블 스캔 접근 방법을 의미한다. 실행 계획의 각 라인에 접근 방법이 2개 이상 표시되지 않으며, index_merge 이외의 type 에서는 인덱스 항목에도 단 하나의 인덱스 이름만 표시된다.

각 접근 방법을 성능이 빠른 순서대로 나열하면 다음과 같다.

• system
• const
• eq_ref
• ref
• fulltext
• ref_or_null
• unique_subquery
• index_subquery
• range
• index_merge
• index
• ALL

system

레코드가 1건만 존재하는 테이블이나 한 건도 존재하지 않는 테이블을 참조하는 형태의 접근 방법이다.

InnoDB 스토리지 엔진을 사용하는 테이블에서는 나타나지 않고, MyISAM 이나 MEMORY 테이블에서만 사용되는 접근 방식이다. 실제 애플리케이션에서 사용되는 쿼리에서는 거의 보이지 않는 실행 계획이다.

const

테이블의 레코드 건수와 관계없이 쿼리가 PK 나 유니크 키 컬럼을 사용하는 WHERE 조건절을 가지고 있으며, 반드시 1건을 반환하는 쿼리의 처리 방식이다. 유니크 인덱스 스캔이라고도 표현한다.

다중 컬럼으로 구성된 PK 나 유니크 키 중에서 인덱스의 일부 컬럼만 조건으로 사용할 때는 const 타입의 접근 방식을 사용할 수 없다. 이 경우에는 실제 레코드가 1건만 저장되어 있더라도 MySQL 엔진이 데이터를 읽어보지 않고서는 레코드가 1건이라는 것을 확신할 수 없기 때문이다.

PK 의 일부만 조건으로 사용할 때는 ref 로 표시된다.
하지만 PK 나 유니크 인덱스의 모든 컬럼을 동등 조건으로 WHERE 절에 명시하면 const 접근 방식을 사용한다.

• [5] 실행 계획의 type 컬럼이 const인 실행 계획은 MySQL 의 옵티마이저가 쿼리를 최적화하는 단계에서 쿼리를 먼저 실행해서 통째로 상수화한다.

eq_ref

여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시된다. 조인에서 처음 읽은 테이블의 컬럼값을 그다음 읽어야 할 테이블의 PK 나 유니크 키 컬럼의 검색 조건에 사용할 때를 가리킨다.

두 번째 이후에 읽히는 테이블을 유니크 키로 검색할 때, 그 유니크 인덱스는 NOT NULL 이어야 하고, 다중 컬럼으로 만들어진 프라이머리 키나 유니크 인덱스라면 인덱스의 모든 컬럼이 비교 조건에 사용되어야만 eq_ref 접근 방법이 사용될 수 있다.

즉, 조인에서 두 번째 이후에 읽는 테이블에서 반드시 1건만 존재한다는 보장이 있어야 사용할 수 있는 접근 방법이다.

ref

eq_ref 와는 달리 조인의 순서와 관계 없이 사용되며, PK 나 유니크 키 등의 제약 조건도 없다. 인덱스 종류와 관계 없이 동등 조건으로 검색할 때는 ref 접근 방식이 사용된다. 반환되는 레코드가 반드시 1건이라는 보장이 없으므로 const 나 eq_ref 보다는 느리다. 그러나 동등한 조건으로만 비교되므로 매우 빠른 레코드 조회 방법 중 하나다.

const, ref, eq_ref 는 모두 매우 좋은 접근 방식으로 인덱스의 분포도가 나쁘지 않다면 성능상의 문제를 일으키지 않는 접근 방식이다.

fulltext

MySQL 서버의 전문 검색 인덱스를 사용해 레코드를 읽는 접근 방식을 의미한다.

전문 검색 조건은 우선순위가 상당히 높다. 쿼리에서 전문 인덱스를 사용하는 조건과 그 이외의 일반 인덱스를 사용하는 조건을 함께 사용하면 일반 인덱스의 접근 방식이 const 나 eq_ref, ref 가 아니면 일반적으로 MySQL 은 전문 인덱스를 사용하는 조건을 선택해서 처리한다.

ref_or_null

ref 접근 방법과 같은데, null 비교가 추가된 형태다. 실제 업무에서 많이 활용되지는 않는다.

unique_subquery

WHERE 조건절에서 사용될 수 있는 IN(subquery) 형태의 쿼리를 위한 접근 방법이다. 서브쿼리에서 중복되지 않는 유니크한 값만 반환할 때 사용한다.

index_subquery

서브쿼리 결과의 중복된 값을 인덱스를 이용해서 제거할 수 있을 때 사용한다.

• unique_subquery: IN (subquery) 형태의 조건에서 subquery 의 반환 값에는 중복이 없으므로 별도의 중복 제거 작업이 필요하지 않음.
• index_subquery: IN(subquery) 형태의 조건에서 subquery 의 반환 값에 중복된 값이 있을 수 있지만 인덱스를 이용해 중복된 값을 제거할 수 있음.

range

인덱스 레인지 스캔 형태의 접근 방법이다. 주로 "<, >, IS NULL, BETWEEN, IN, LIKE" 등의 연산자를 이용해 인덱스를 검색할 때 사용된다. 일반적으로 애플리케이션의 쿼리가 가장 많이 사용하는 접근 방법이다.

range 접근 방식도 상당히 빠르다.

index_merge

2개 이상의 인덱스를 이용해 각각의 검색 결과를 만들어낸 후, 그 결과를 병합해서 처리하는 방식이다.

다음과 같은 특징이 있다.

• 여러 인덱스를 읽어야 하므로 일반적으로 range 접근 방식보다 효율성이 떨어진다.
• 전문 검색 인덱스를 사용하는 쿼리에서는 index_merge 가 적용되지 않는다.
• index_merge 접근 방법으로 처리된 결과는 항상 2개 이상의 집합이 되기 때문에 그 두 집합의 교집합이나 합집합, 중복 제거와 같은 부가적인 작업이 더 필요하다.

index

인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔을 의미한다. range 접근 방식 같이 효율적으로 인덱스의 필요한 부분만 읽는 것을 의미하지는 않는다.

테이블을 처음부터 끝까지 읽는 풀 테이블 스캔 방식과 비교했을 때 비교하는 레코드 건수는 같다. 그러나 인덱스는 일반적으로 데이터 파일 전체보다 크기가 작으므로 인덱스 풀 스캔 시 풀 테이블 스캔보다 빠르게 처리되며, 쿼리의 내용에 따라 정렬된 인덱스의 장점을 이용할 수 있으므로 훨씬 효율적이다.

다음 조건 가운데 첫 번째 + 두 번째 조건을 충족하거나, 첫 번째 + 세 번째 조건을 충족하는 쿼리에서 사용되는 읽기 방식이다.

• range 나 const, ref 같은 접근 방식으로 인덱스를 사용하지 못하는 경우
• 인덱스에 포함된 컬럼만으로 처리할 수 있는 쿼리인 경우 (데이터 파일을 읽지 않아도 되는 경우)
• 인덱스를 이용해 정렬이나 grouping 작업이 가능한 경우 (별도의 정렬 작업을 피할 수 있는 경우)

ALL

테이블을 처음부터 끝까지 전부 읽어서 불필요한 레코드를 제거하고 반환한다. 가장 비효율적인 방법이다.

InnoDB 도 풀 테이블 스캔이나 인덱스 풀 스캔과 같은 대량의 디스크 I/O 를 유발하는 작업을 위해 한 번에 많은 페이지를 읽어들이는 기능을 제공한다. 이를 Read Ahead 라고 하며, 한 번에 여러 페이지를 읽어서 처리할 수 있다.

일반적으로 Index 나 ALL 접근 방식은 작업 범위를 제한하는 조건이 아니므로, 빠른 응답을 사용자에게 보내야 하는 웹 서비스와 같은 온라인 트랜잭션 처리 환경에는 적합하지 않다.